package com.gitee.loyo.act

import com.gitee.loyo.KSQL
import com.gitee.loyo.interfaces.ParameterizeSQL

open class ModelSQLBuilder<M: Model<M, R>, R: Record<M, R>>(
    private val tableName: String
) {

    @delegate: Transient val inner: ModelJoiner by lazy { ModelJoiner("inner") }
    @delegate: Transient val left: ModelJoiner by lazy { ModelJoiner("left") }
    @delegate: Transient val right: ModelJoiner by lazy { ModelJoiner("right") }
    @Transient internal var selectColumns: ArrayList<Column<*>>? = null

    internal var alias: String = ""
    internal var selectClause: List<String>? = null
    internal var joinClause: StringBuilder? = null
    internal var insertIntoClause: ArrayList<String>? = null
    internal var updateSetClause: ArrayList<String>? = null
    internal var whereClause: ArrayList<String>? = null
    internal var groupClause: ArrayList<String>? = null
    internal var havingClause: ArrayList<String>? = null
    internal var orderClause: ArrayList<String>? = null
    internal var limitClause: String? = null
    internal var lock: Lock = Lock.NONE
    internal var parameters: ArrayList<Any?> = arrayListOf()
    internal var pattern: String = ""
    internal var sqlPartList: ArrayList<SQLPart>? = null

    internal fun getTableNameWithAlias(): String{
        return if(alias.isEmpty()){
            "$tableName$pattern"
        }else{
            "$tableName$pattern $alias"
        }
    }

    internal fun getTableName(): String{
        return "$tableName$pattern"
    }

    open fun select(vararg columns: Column<*>){
        selectColumns?.also {
            columns.forEach {
                if(it is LiteralColumn){
                    parameters.add(it.value)
                }
                selectColumns!!.add(it)
            }
        }
    }

    open fun where(fn: M.() -> Unit){
        whereClause?.also {
            appendWhereClause {
                fn(this)
            }
        }
    }

    open fun group(vararg columns: Column<*>){
        groupClause?.also {
            columns.forEach {
                groupClause!!.add(it.getLabelName())
            }
        }
    }

    open fun order(fn: M.( (Column<*>) -> Unit, (Column<*>) -> Unit ) -> Unit){
        orderClause?.also {
            fn(this as M, {
                orderClause!!.add("${it.getLabelName()} asc")
            }, {
                orderClause!!.add("${it.getLabelName()} desc")
            })
        }
    }

    open fun range(range: Pair<Int, Int>){
        if(range.first <= range.second) {
            limit(range.first - 1, range.second - range.first + 1)
        }else {
            throw IllegalArgumentException("${range.first} > ${range.second}")
        }
    }

    open fun limit(limit: Int){
        limit(0, limit)
    }

    open fun limit(offset: Int, limit: Int){
        when(KSQL.dialect){
            com.gitee.loyo.Dialect.Mysql -> {
                limitClause = if(offset == 0){
                    "limit $limit"
                }else{
                    "limit $offset, $limit"
                }
            }
            com.gitee.loyo.Dialect.Postgresql -> {
                limitClause = if(offset == 0){
                    "limit $limit"
                }else{
                    "limit $limit offset $offset"
                }
            }
        }

    }

    open fun ifNotNull(sqlPart: SQLPart){
        if(sqlPart.getParameters().isEmpty()){
            sqlPart.disable()
            return
        }
        for (parameter in sqlPart.getParameters()) {
            if(parameter == null){
                sqlPart.disable()
                return
            }
        }
    }

    open fun having(fn: M.() -> Unit){
        havingClause?.also {
            appendHavingClause {
                fn.invoke(this)
            }
        }
    }

    open fun set(vararg keyValues: Pair<Column<out Any>, Any?>){
        updateSetClause?.also {
            appendSetClause(keyValues.toList())
        }
    }

    internal fun appendValue(name: String, op: String, value: Any) {
        if(whereClause != null) {
            whereClause!!.add("$name $op ?")
            parameters.add(value)
        }
    }

    internal fun toSelectParameterizeSQL(): ParameterizeSQL {
        return object : ParameterizeSQL {
            override fun getSQL(): String {
                val builder = StringBuilder()
                if(selectColumns != null){
                    selectClause = selectColumns!!.map{
                        it.getLabelName()
                    }
                }
                selectClause?.ifEmpty { null }?.also {
                    builder.append("select ").append(it.joinToString(", ")).append(" ")
                }?:also {
                    builder.append("select ").append(allColumnString()).append(" ")
                }
                builder.append("from ").append(getTableNameWithAlias()).append(" ")
                joinClause?.ifEmpty { null }?.also {
                    builder.append(it).append(" ")
                }
                whereClause?.ifEmpty { null }?.also {
                    builder.append("where ").append(it.joinToString(" and ")).append(" ")
                }
                groupClause?.ifEmpty { null }?.also{
                    builder.append("group by ").append(it.joinToString(", ")).append(" ")
                }
                havingClause?.ifEmpty { null }?.also{
                    builder.append("having ").append(it.joinToString(" and ")).append(" ")
                }
                orderClause?.ifEmpty { null }?.also{
                    builder.append("order by ").append(it.joinToString(", ")).append(" ")
                }
                limitClause?.ifEmpty { null }?.also{
                    builder.append(it).append(" ")
                }
                when(lock){
                    Lock.IN_SHARE_MODE -> when(KSQL.dialect){
                        com.gitee.loyo.Dialect.Postgresql -> KSQL.tryGetConnection().apply {
                            val executeOnce = this.executeOnce
                            execute("lock table ${getTableName()} in share mode")
                            this.executeOnce = executeOnce
                        }
                        com.gitee.loyo.Dialect.Mysql -> builder.append("lock in share mode")
                    }
                    Lock.FOR_UPDATE -> builder.append("for update")
                }
                return builder.toString()
            }

            override fun getParameters(): Array<Any?> {
                return parameters.toArray()
            }

        }
    }

    internal fun toInsertParameterizeSQL(): ParameterizeSQL {
        return object : ParameterizeSQL {
            override fun getSQL(): String {
                val builder = StringBuilder();
                builder.append("insert into ").append(getTableName()).append("(")
                insertIntoClause?.ifEmpty { null }?.also{
                    builder.append(it.joinToString(", "))
                        .append(")values(")
                        .append(it.joinToString(", "){ "?" })
                        .append(")")
                }?:throw UnsupportedOperationException("${getTableName()}未保存任何字段")


                return builder.toString()
            }

            override fun getParameters() = parameters.toArray()

        }
    }

    internal fun toUpdateParameterizeSQL(): ParameterizeSQL {
        return object : ParameterizeSQL {
            override fun getSQL(): String {
                val builder = StringBuilder();
                builder.append("update ").append(getTableName()).append(" ")
                updateSetClause?.ifEmpty { null }?.also{
                    builder.append("set ").append(it.joinToString(", ")).append(" ")
                }?:throw UnsupportedOperationException("${getTableName()}未更新任何语句")

                whereClause?.ifEmpty { null }?.also{
                    builder.append("where ").append(it.joinToString(" and "))
                }
                return builder.toString()
            }

            override fun getParameters() = parameters.toArray()

        }
    }

    internal fun toDeleteParameterizeSQL(): ParameterizeSQL {
        return object : ParameterizeSQL {
            override fun getSQL(): String {
                val builder = StringBuilder();
                builder.append("delete from ").append(getTableNameWithAlias()).append(" ")
                whereClause?.ifEmpty { null }?.also{
                    builder.append("where ").append(it.joinToString(" and "))
                }
                return builder.toString()
            }

            override fun getParameters() = parameters.toArray()

        }
    }

    fun addToSelectClause(column: Column<*>) {
        selectColumns?.add(column)
    }

    fun addToOrderClause(column: Column<*>, direction: String) {
        orderClause?.add("${column.getLabelName()} $direction")
    }

    fun addParameter(value: Any?) {
        parameters.add(value)
    }

    fun addParameters(value: Array<*>) {
        parameters.addAll(value)
    }

    internal fun appendSetClause(propValues: List<Pair<Column<out Any>, Any?>>) {
        updateSetClause?.also {
            propValues.forEach { (col, v) ->
                updateSetClause!!.add(col.columnName + " = ?")
                parameters.add(v)
            }
        }
    }

    internal fun appendInsertIntoClause(propValues: List<Pair<Column<out Any>, Any>>) {
        insertIntoClause?.also {
            propValues.forEach { (col, v) ->
                insertIntoClause!!.add(col.columnName)
                parameters.add(v)
            }
        }
    }

    internal fun appendWhereClause(fn: M.() -> Unit?) {
        sqlPartList = arrayListOf()
        fn(this as M)
        sqlPartList?.filter { it.isEnabled() }?.forEach {
            if(it.isConnected()){
                whereClause!!.add("(${it.getString()})")
            }else {
                whereClause!!.add(it.getString())
            }
            parameters.addAll(it.getParameters())
        }
        sqlPartList = null
    }

    internal fun appendHavingClause(fn: M.() -> Unit?) {
        sqlPartList = arrayListOf()
        fn(this as M)
        sqlPartList?.filter { it.isEnabled() }?.forEach {
            if(it.isConnected()){
                havingClause!!.add("(${it.getString()})")
            }else {
                havingClause!!.add(it.getString())
            }
            parameters.addAll(it.getParameters())
        }
        sqlPartList = null
    }

    fun addSQLPart(sqlPart: SQLPart) {
        sqlPartList?.add(sqlPart)
    }

    internal fun allColumnString(): String {
        return if(alias.isEmpty()){
            "*"
        }else{
            "$alias.*"
        }
    }

    internal fun getColumn(fieldName: String): Column<Any>? {
        val columnName = KSQL.nameMappingStrategy.toColumnName(fieldName)
        return KSQL.reflection.getFieldValue(this, columnName)?.let { it as Column<Any> }
    }

    internal fun enable(vararg clauses: Clause){
        for (clause in clauses) {
            when(clause){
                Clause.Select -> selectColumns = ArrayList()
                Clause.Join -> joinClause = StringBuilder()
                Clause.Where -> whereClause = ArrayList()
                Clause.Group -> groupClause = ArrayList()
                Clause.Having -> havingClause = ArrayList()
                Clause.Order -> orderClause = ArrayList()
                Clause.InsertInto -> insertIntoClause = ArrayList()
                Clause.UpdateSet -> updateSetClause = ArrayList()
            }
        }
    }

    inner class ModelJoiner(
        private val joinType: String = "inner"
    ){


        infix fun <N: Model<N, *>> join(model: ModelCompanion<N, *>): ModelJoinRel<N>{
            joinClause?.also {
                if(joinClause!!.isNotEmpty()){
                    joinClause!!.append(" ")
                }

                alias = getTableName()

                val joinModel = model.createModel()
                joinModel.alias = joinModel.getTableName()
                if(joinModel.alias == alias){
                    throw UnsupportedOperationException("不支持join同一个表")
                }
                joinClause!!.append(joinType).append(" join ").append(joinModel.getTableName()).append(" ")

                return ModelJoinRel(joinModel)
            }

            throw UnsupportedOperationException("无法操作join")
        }
    }

    inner class ModelJoinRel<N: Model<N, *>>(
        private val joinModel: N
    ){

        infix fun on(column: Column<*>): ModelJoinFilter<N>{
            joinClause?.also {
                joinClause!!.append("on ")
                    .append(alias).append(".").append(column.columnName)
                    .append(" = ")
                    .append(joinModel.alias).append(".").append(column.columnName)
                    .append(" ")

                return ModelJoinFilter(joinModel)
            }

            throw UnsupportedOperationException("无法操作join")
        }
    }

    inner class ModelJoinFilter<N: Model<N, *>>(
        private val joinModel: N
    ){

        infix fun and(fn: N.() -> Unit){
            joinClause?.also {
                joinModel.whereClause = ArrayList()
                joinModel.where(fn)
                parameters.addAll(joinModel.parameters)
                joinClause!!.append("and ").append(joinModel.whereClause!!.joinToString(" and ")).append(" ")
            }
        }
    }
}

enum class Clause{
    Select, Join, Where, Group, Having, Order, InsertInto, UpdateSet
}